﻿CREATE PROCEDURE [dbo].[GoodsForDefekturaSelect]
AS
DECLARE @tblGoods TABLE (
Id int NOT NULL, 
Name varchar(300) NULL, 
CategoryId int NULL, 
MarkUp real null,
PackQntty int null,
SaleDays int NULL,
FullDays int NULL,
PresenceCoef real NULL,
Note varchar(250) NULL, 
ChangeDateTime datetime null,
ChangeWhom varchar(50) NULL
PRIMARY KEY (	Id) )

--показывать только те товары которые были в обороте и родительские товары
INSERT INTO @tblGoods (Id, Name, PackQntty, Note, ChangeDateTime, ChangeWhom)
SELECT G.Id, G.FullName + ( CASE WHEN G.ManufacturerName = 'Неопределено' THEN '' ELSE '-' + G.ManufacturerName END)
+ ( CASE WHEN G.CountryName = 'Неопределено' THEN '' ELSE '-' + G.CountryName END) AS Name,
P.WholesalePack, P.Note, P.MD, P.MW
FROM dbo.vwGoodsDetailed AS G 
INNER JOIN CA.Goods AS P ON G.Id = P.Id 
INNER JOIN (
	SELECT DISTINCT GoodsId FROM CD.Parties --только те товары которые были в обороте
	UNION
	SELECT GoodsId FROM CD.InputLines --и в приходе
) F ON G.Id = F.GoodsId 

UPDATE D SET D.CategoryId = S.DefekturaCategoryId,
D.SaleDays = S.SaleDays,
D.FullDays = S.FullDays,
D.PresenceCoef = S.PresenceCoef
FROM @tblGoods D INNER JOIN PR.DefekturaGoods S ON D.Id = S.GoodsId

SELECT ID, cast(Id as varchar(10)) as AssortmentId, Name, CategoryId, 
SaleDays, FullDays, PresenceCoef,
PackQntty, Note, ChangeDateTime, ChangeWhom FROM @tblGoods

RETURN 0
